<?
/*
Bitsand - a web-based booking system for LRP events
Copyright (C) 2006, 2007 Russell Peter Phillips

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
*/

//Export bookings list to csv
include ('inc_head_db.php');
include ('inc_admin.php');

//Send headers to tell browser that this is a CSV file
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=bookings.csv;");

//Get list of players marked as paid
$key = CRYPT_KEY;
$sql = "SELECT plPlayerID, " . 
	"AES_DECRYPT(plFirstName, '$key') AS dFirstName, " .
	"AES_DECRYPT(plSurname, '$key') AS dSurname, " .
	"AES_DECRYPT(plAddress1, '$key') AS dAddress1, " .
	"AES_DECRYPT(plAddress2, '$key') AS dAddress2, " .
	"AES_DECRYPT(plAddress3, '$key') AS dAddress3, " .
	"AES_DECRYPT(plAddress4, '$key') AS dAddress4, " .
	"AES_DECRYPT(plPostcode, '$key') AS dPostcode, " .
	"AES_DECRYPT(plTelephone, '$key') AS dTelephone, " .
	"AES_DECRYPT(plMobile, '$key') AS dMobile, " .
	"AES_DECRYPT(plEmail, '$key') AS dEmail, " .
	"AES_DECRYPT(plDOB, '$key') AS dDOB, " .
	"AES_DECRYPT(plMedicalInfo, '$key') AS dMedicalInfo, " .
	"AES_DECRYPT(plEmergencyName, '$key') AS dEmergencyName, " .
	"AES_DECRYPT(plEmergencyNumber, '$key') AS dEmergencyNumber, " .
	"AES_DECRYPT(plEmergencyRelationship, '$key') AS dEmergencyRelationship, " .
	"AES_DECRYPT(plDietary, '$key') AS dDietary, " .
	"AES_DECRYPT(plBookAs, '$key') AS dBookAs, " .
	"chName, chRace, chGender, chGroupSel, chGroupText, chFaction, chFactionRank, chGuilds, chAncestor, chIsle, chNPC, chNotes, chOSP, " .
	"bkDatePaymentConfirmed " .
	"FROM players, characters, bookings " .
	"WHERE plPlayerID = chPlayerID AND chPlayerID = bkPlayerID AND bkDatePaymentConfirmed <> '0000-00-00' " .
	"ORDER BY dBookAs, dSurname";
$result = mysqli_query ($link, $sql);

//Header row
echo '"Player Name","Address 1","Address 2","Address 3","Address 4","Postcode","Telephone","Mobile","email","dob","NOK","NOK contact",';
echo '"NOK Relationship","Medical","Diet","Booking As","Character name","Gender","Race","Group","Faction","Rank","Guild","Ancestor",';
echo '"Island","IC Notes",';
//Skills ordered by skID
echo '"Small Weapons","Spellcasting 1","One-Handed Weapons","Spellcasting 2","Two Handed Weapons","Incantation 1",';
echo '"Projectile Weapons","Incantation 2","Thrown Weapons","Healing","Ambidexterity","Invocation","Shield Use","Sense Magic",';
echo '"Light Armour","Evaluate","Medium Armour","Potion Lore","Heavy Armour","Poison Lore","Body Development","Recognise Forgery",';
echo '"Bind Wounds","Literacy","Physician","Numeracy","Make/Read Maps","Ritual Magic","Contribute to Ritual","OSPs",';
echo '"Date Paid"' . "\n";

while ($row = mysqli_fetch_assoc ($result)) {
	echo '"' . stripslashes ($row ['dFirstName'] . ' ' . $row ['dSurname']) . '",';
	echo '"' . stripslashes ($row ['dAddress1']) . '",';
	echo '"' . stripslashes ($row ['dAddress2']) . '",';
	echo '"' . stripslashes ($row ['dAddress3']) . '",';
	echo '"' . stripslashes ($row ['dAddress4']) . '",';
	echo '"' . stripslashes ($row ['dPostcode']) . '",';
	echo '"' . stripslashes ($row ['dTelephone']) . '",';
	echo '"' . stripslashes ($row ['dMobile']) . '",';
	echo '"' . stripslashes ($row ['dEmail']) . '",';
	$dDOB = stripslashes ($row ['dDOB']);
	$iYear = substr ($dDOB, 0, 4);
	$iMonth = substr ($dDOB, 4, 2);
	$iDate = substr ($dDOB, 6, 2);
	$sDOB = "$iDate-$iMonth-$iYear";
	echo '"' . $sDOB . '",';
	echo '"' . stripslashes ($row ['dEmergencyName']) . '",';
	echo '"' . stripslashes ($row ['dEmergencyNumber']) . '",';
	echo '"' . stripslashes ($row ['dEmergencyRelationship']) . '",';
	echo '"' . stripslashes ($row ['dMedicalInfo']) . '",';
	echo '"' . stripslashes ($row ['dDietary']) . '",';
	echo '"' . stripslashes ($row ['dBookAs']) . '",';
	echo '"' . stripslashes ($row ['chName']) . '",';
	echo '"' . stripslashes ($row ['chGender']) . '",';
	echo '"' . stripslashes ($row ['chRace']) . '",';
	echo '"' . stripslashes ($row ['chGroupSel'] . ' (' . $row ['chGroupText']) . ')",';
	echo '"' . stripslashes ($row ['chFaction']) . '",';
	echo '"' . stripslashes ($row ['chFactionRank']) . '",';
	echo '"' . stripslashes ($row ['chGuilds']) . '",';
	echo '"' . stripslashes ($row ['chAncestor']) . '",';
	echo '"' . stripslashes ($row ['chIsle']) . '",';
	echo '"' . stripslashes ($row ['chNotes']) . '",';
	//Get this character's skills. Fill two arrays with the skills. These arrays can then be queried
	$skillstaken = mysqli_query ($link, "SELECT * FROM skillstaken WHERE stPlayerID = " . $row ['plPlayerID']);
	//$aiSkillID will hold the skill ID's. $aiPoints will hold the points spent
	$aiSkillID = array ();
	$aiPoints = array ();
	while ($record = mysqli_fetch_assoc ($skillstaken)) {
		$iID = $record ['stSkillID'];
		$iPoints = $record ['stPoints'];
		$aiSkillID [] = $iID;
		$aiPoints [] = $iPoints;
	}
	$skills = mysqli_query ($link, 'SELECT * FROM skills ORDER BY skID');
	while ($record = mysqli_fetch_assoc ($skills)) {
		//Find out if character has this skill
		$has = array_search ($record ['skID'], $aiSkillID);
		if ($has !== False)
			//Character has the skill. Echo how many points they spent
			echo '"' . $aiPoints [$has]. '",';
		else
			//Character does not have the skill. Echo empty cell
			echo '"",';
	}
	echo '"' . stripslashes ($row ['chOSP']) . '",';
	//Date payment received
	$dPaid = stripslashes ($row ['bkDatePaymentConfirmed']);
	$iYear = substr ($dPaid, 0, 4);
	$iMonth = substr ($dPaid, 5, 2);
	$iDate = substr ($dPaid, 8, 2);
	$sPaid = "$iDate-$iMonth-$iYear";
	echo '"' . $sPaid . '"' . "\n";
}
?>
